package com.xm.dao.impl;

import com.xm.dao.GoodsCommentDao;
import com.xm.dao.GoodsDao;
import com.xm.po.Goods;
import com.xm.util.BaseDao;
import com.xm.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 * @date 2020-08-07-15:15
 * @description 商品数据操作接口实现类
 */
public class GoodsDaoImpl implements GoodsDao {
    @Override
    public boolean add(Goods goods) {
        return DButil.insert(goods);
    }

    @Override
    public Goods getGoodsBySn(String goodSn) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Goods goods = new Goods();
        String sql = "select * from goods where goodsSn = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,goodSn);
            rs = ps.executeQuery();
            goods = (Goods) DButil.getRsOne(Goods.class,rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return goods;
    }

    @Override
    public Goods getGoodsByGoodsId(Integer goodsId) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Goods goods = new Goods();
        String sql = "select * from goods where goodsId = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,goodsId);
            rs = ps.executeQuery();
            goods = (Goods) DButil.getRsOne(Goods.class,rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return goods;
    }

    @Override
    public List<Goods> getGoodsByTypeTop6(Integer typeId) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Goods> list = new ArrayList<>();
        String sql = "select * from goods where goodsTypeId = ? order by saleCount limit 6";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,typeId);
            rs = ps.executeQuery();
            list = (List<Goods>) DButil.getRsData(Goods.class,rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return list;
    }

    @Override
    public List<Goods> getGoodsBySearchKey(String key,String orderBy, Integer pageNo, Integer pageSize) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Goods> list = new ArrayList<>();
        String sql = "select * from goods where goodsName like ? order by ? desc limit ?,?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"%"+key+"%");
            ps.setObject(2,orderBy);
            ps.setObject(3,(pageNo-1)*pageSize);
            ps.setObject(4,pageSize);
            rs = ps.executeQuery();
            list = (List<Goods>) DButil.getRsData(Goods.class,rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return list;
    }

    @Override
    public Integer getCount(String key) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer count = null;
        String sql = "select count(*) from goods where goodsName like ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"%"+key+"%");
            rs = ps.executeQuery();
            if (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return count;
    }

    @Override
    public List<Goods> getAll() {
        List<Goods>list= (List<Goods>) DButil.queryAll(Goods.class);
        return list;
    }
    /**
     * 修改商品信息
     * @param goods
     * @return boolean
     */
    @Override
    public boolean update(Goods goods) {
        return DButil.update(goods);
    }

    @Override
    public List<Goods> getGoodsByLike(String key) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Goods> list = new ArrayList<>();
        String sql = "select * from goods where goodsName like ? order by saleCount desc limit 4";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"%"+key+"%");
            rs = ps.executeQuery();
            list = (List<Goods>) DButil.getRsData(Goods.class,rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return list;
    }
    /**
     * 根据编号和名字、类型查询
     * @param id
     * @param name
     * @return
     */
    @Override
    public Goods queryOne(String id, String name, String type) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM goods WHERE goodsId=? OR goodsName=? OR goodsTypeId = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            ps.setObject(2,name);
            ps.setObject(3,type);
            rs = ps.executeQuery();
            Goods rsOne = (Goods) DButil.getRsOne(Goods.class, rs);
            return rsOne;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return null;
    }

    /**
     * 根据编号和名字查询
     * @param id
     * @param name
     * @return
     */
    @Override
    public List<Goods> queryOne(String id, String name) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM goods WHERE goodsId=? OR goodsName=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            ps.setObject(2,name);
            rs = ps.executeQuery();
            List<Goods> rsOne = (List<Goods>) DButil.getRsOne(Goods.class, rs);
            return rsOne;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return null;
    }
    /**
     * 根据编号查询商品
     * @param id
     * @return
     */
    @Override
    public Goods queryById(String id) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM goods WHERE goodsSn=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            Goods rsOne = (Goods) DButil.getRsOne(Goods.class, rs);
            return rsOne;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return null;
    }
    /**
     * 根据名称查询商品
     * @param name
     * @return
     */
    @Override
    public Goods queryByName(String name) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM goods WHERE goodsName LIKE ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"%"+name+"%");
            rs = ps.executeQuery();
            Goods rsOne = (Goods) DButil.getRsOne(Goods.class, rs);
            return rsOne;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return null;
    }
    /**
     * 根据商品类型查询商品信息
     * @return
     */
    @Override
    public List<Goods> queryByGoodsType(String type) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM goods WHERE goodsTypeId=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,type);
            rs = ps.executeQuery();
            List<Goods> rsOne = (List<Goods>) DButil.getRsData(Goods.class, rs);
            return rsOne;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return null;
    }
    /**
     * 根据id修改isSale(下架)
     * @param goodsId
     * @return
     */
    @Override
    public boolean soldOut(Integer goodsId) {
        Connection conn = DButil.getConnection();
        PreparedStatement ps = null;
        String sql = "update goods set isSale = 0 where goodsId = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,goodsId);
            int i = ps.executeUpdate();
            return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

}
